import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Scanner;

public class Test_JDBC {
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";
    private static DataSource dataSource=null;

    public static DataSource getDataSource() {
        if(dataSource==null) {
            DataSource dataSource=new MysqlDataSource();
            ((MysqlDataSource)dataSource).setUrl(URL);
            ((MysqlDataSource)dataSource).setUser(USERNAME);
            ((MysqlDataSource)dataSource).setPassword(PASSWORD);
        }
        return dataSource;
    }
    public static Connection getConnection() throws SQLException{
        return (Connection) getDataSource().getConnection();
    }
    public static PreparedStatement getStatement(String myInsert) throws  SQLException {
        PreparedStatement statement_insert=getConnection().prepareStatement(myInsert);
        return  statement_insert;
    }


    public static String mysqlInsert(String categoryOrStudentTable, int categoryIdOrStudentId, String categoryOrStudentName) {
        String myInsert = new String("insert into " + categoryOrStudentTable + " values (" + categoryIdOrStudentId + ',' + categoryOrStudentName + ");");
        return myInsert;
    }

    public static String mysqlInsert(int bookId, String bookName, String bookWriter, double bookPrice, int bookType) {
        String myInsert = new String("insert into book values (" + bookId + ',' + bookName + ',' + bookWriter + ',' + bookPrice + ',' + bookType + ");");
        return myInsert;
    }

    static int inforId = 1;

    public static String mysqlInsert(int bookId, int student_Id, String start_time, String end_time) {
        String myInsert =new String("insert into book values (" +inforId+','+bookId+','+start_time+','+end_time+");");
        inforId++;
        return  myInsert;
    }

    public static void myClose(Connection connection, PreparedStatement statement, ResultSet resultSet)throws  SQLException{
        if (resultSet!=null) {
            resultSet.close();
        }
        if (statement!=null) {
            statement.close();
        }
        if(connection!=null) {
            connection.close();
        }
    }

    public static void main(String[] args) throws SQLException {
        //1.创建数据源。数据源描述了你要操作的数据库，在哪里.
        dataSource=getDataSource();
        // 2. 和数据库服务器，建立连接.
        Connection connection=dataSource.getConnection();

        // 3.构造一个 SQL
        // 建表
        String sql1=
                "CREATE TABLE category (\n" +
                "\n" +
                " id int(11) PRIMARY KEY AUTO_INCREMENT,\n" +
                "\n" +
                " name varchar(20)\n" +
                "\n" +
                ");";

        String sql2= "CREATE TABLE student (\n" +
                "\n" +
                " id int(11) PRIMARY KEY AUTO_INCREMENT,\n" +
                "\n" +
                " name varchar(20)\n" +
                "\n" +
                ");";

        String sql3= "CREATE TABLE book (\n" +
                "\n" +
                " id int(11) PRIMARY KEY AUTO_INCREMENT,\n" +
                "\n" +
                " name varchar(20),\n" +
                "\n" +
                " author varchar(20),\n" +
                "\n" +
                " price decimal(10, 2),\n" +
                "\n" +
                " category_id int(11),\n" +
                "\n" +
                " CONSTRAINT fk_book_category_id FOREIGN KEY (category_id) REFERENCES category (id)\n" +
                "\n" +
                ");\n" +
                "\n";

        String sql4= "CREATE TABLE borrow_info (\n" +
                "\n" +
                " id int(11) PRIMARY KEY AUTO_INCREMENT,\n" +
                "\n" +
                " book_id int(11),\n" +
                "\n" +
                " student_id int(11),\n" +
                "\n" +
                " start_time timestamp null,\n" +
                "\n" +
                " end_time timestamp null,\n" +
                "\n" +
                " CONSTRAINT fk_borrow_info_book_id FOREIGN KEY (book_id) REFERENCES book (id),\n" +
                "\n" +
                " CONSTRAINT fk_borrow_info_student_id FOREIGN KEY (student_id) REFERENCES student (id)\n" +
                "\n" +
                ");\n" +
                "\n";

        //插入数据

        String sql_insert1=mysqlInsert("category",1,"百年孤独");




        // 需要把 string sql 转成 语句对象
        PreparedStatement statement1=connection.prepareStatement(sql1);



        PreparedStatement statement_insert1=getStatement(sql_insert1);


        // 4.把构造好的 SQL 发送给服务器去执行




        // 5.最后一步，释放必要的资源
        statement1.close();
        connection.close();



    }
}
